#!/bin/bash
#声明当前脚本使用bash解释器质性
#接收外部传入的日期参数，如果外部没有传入日期参数，怎么办？
#严格:外部没有传入，就退出 exit  非严格：如果外部没有传入，自己生成一个默认的日期(当前机器时间的昨天)
#-n代表判断字符串的长度，如果字符串长度为0，返回false,否则返回true
if [ -n "$1" ]
then
	do_date=$1
else
	#echo 必须给我传一个日期
	#exit
	do_date= $(date -d yesterday +%F) 
fi

echo 当前要操作的日期是$do_date
ads_play_video_by_chapter="
    insert overwrite table ads_play_video_by_chapter
select
    `dt`         ,
    `recent_days`,
    chapter_name   ,
    sum(user_count)    user_cout,  --观看人数
    sum(sum_play_sec)/sum(user_count) avg_sec,  --人均观看时长
    sum(sid_count)     sid_count  --播放视频次数
from dws_play_course_video_1d
lateral view explode(`array`(1,7,30)) tmp as recent_days
where dt>date_sub($do_date,30)
    and dt>date_sub($do_date,recent_days)
group by chapter_name,recent_days,dt;
"
ads_play_video_by_course="
    insert overwrite table ads_play_video_by_course
select
    `dt`         ,
    `recent_days`,
    course_name   ,
    sum(user_count)    user_cout,  --观看人数
    sum(sum_play_sec)/sum(user_count) avg_sec,  --人均观看时长
    sum(sid_count)     sid_count  --播放视频次数
from dws_play_course_video_1d
lateral view explode(`array`(1,7,30)) tmp as recent_days
where dt>date_sub($do_date,30)
    and dt>date_sub($do_date,recent_days)
group by course_name,recent_days,dt;
"
ads_play_course_by_course="
    insert overwrite table ads_play_course_by_course
select
    `dt`         ,
    `recent_days`,
    course_name   ,
    sum(counted_user)    counted_user,  --完课人次
    sum(counted_user)/sum(count_user) avg_chapter,  --人均完成章节数
    sum(count_user)     count_user  --完课人数
from dws_play_course_course_1d
lateral view explode(`array`(1,7,30)) tmp as recent_days
where dt>date_sub($do_date,30)
    and dt>date_sub($do_date,recent_days)
group by course_name,recent_days,dt;
"
ads_play_course="
    insert overwrite table ads_play_course
select
    `dt`         ,
    `recent_days`,
    sum(counted_user)    counted_user,  --观看人数
    sum(count_user)     count_user  --完课人数
from dws_play_course_course_1d
lateral view explode(`array`(1,7,30)) tmp as recent_days
where dt>date_sub($do_date,30)
    and dt>date_sub($do_date,recent_days)
group by recent_days,dt;
"
ads_page_pathr="
DROP TABLE IF EXISTS ads_page_path;
CREATE EXTERNAL TABLE ads_page_path
(
    `dt`          STRING COMMENT '统计日期',
    `recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `source`      STRING COMMENT '跳转起始页面ID',
    `target`      STRING COMMENT '跳转终到页面ID',
    `path_count`  BIGINT COMMENT '跳转次数'
) COMMENT '页面浏览路径分析'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_page_path/';

insert overwrite table ads_page_path
select * from ads_page_path
union
select
    '2022-07-04' dt,
    recent_days,
    source,
    nvl(target,'null'),
    count(*) path_count
from
(
    select
        recent_days,
        concat('step-',rn,':',page_id) source,
        concat('step-',rn+1,':',next_page_id) target
    from
    (
        select
            recent_days,
            page_id,
            lead(page_id,1,null) over(partition by session_id,recent_days) next_page_id,
            row_number() over (partition by session_id,recent_days order by view_time) rn
        from dwd_traffic_page_view_inc lateral view explode(array(1,7,30)) tmp as recent_days
        where dt>=date_add('2022-07-04',-recent_days+1)
    )t1
)t2
group by recent_days,source,target;
"
ads_traffic_stats_by_channel="
DROP TABLE IF EXISTS ads_traffic_stats_by_channel;
CREATE EXTERNAL TABLE ads_traffic_stats_by_channel
(
    `dt`               STRING COMMENT '统计日期',
    `recent_days`      BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
    `channel`          STRING COMMENT '渠道',
    `uv_count`         BIGINT COMMENT '访客人数',
    `avg_duration_sec` BIGINT COMMENT '会话平均停留时长，单位为秒',
    `avg_page_count`   BIGINT COMMENT '会话平均浏览页面数',
    `sv_count`         BIGINT COMMENT '会话数',
    `bounce_rate`      DECIMAL(16, 2) COMMENT '跳出率'
) COMMENT '各渠道流量统计'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    LOCATION '/warehouse/gmall/ads/ads_traffic_stats_by_channel/';

insert overwrite table ads_traffic_stats_by_channel
select * from ads_traffic_stats_by_channel
union
select
    'do_date' dt,
    recent_days,
    channel,
    cast(count(distinct(mid_id)) as bigint) uv_count,
    cast(avg(during_time_1d)/1000 as bigint) avg_duration_sec,
    cast(avg(page_count_1d) as bigint) avg_page_count,
    cast(count(*) as bigint) sv_count,
    cast(sum(if(page_count_1d=1,1,0))/count(*) as decimal(16,2)) bounce_rate
from dws_traffic_session_page_view_1d lateral view explode(array(1,7,30)) tmp as recent_days
where dt>=date_add('do_date',-recent_days+1)
group by recent_days,channel;
"
ads_user_change"
insert overwrite table ads_user_change
select * from ads_user_change
union
select
    'do_date' dt,
    user_churn_count,
    user_back_count
from
(
    select count(*) user_churn_count
    from dws_user_user_login_td
    where dt = 'do_date'
      and datediff('do_date', login_date_last) = 7
) t3
join
(
    select
           count(*) user_back_count
    from
    (
        select user_id
        from dws_user_user_login_td
        where dt = 'do_date' and login_date_last = 'do_date'
    ) t1
    join
    (
        select
            user_id
        from dws_user_user_login_td
        where dt = date_sub('do_date', 1) and login_date_last <= date_sub('do_date', 8)
    ) t2
    on t1.user_id = t2.user_id
) t4;
"
ads_user_retention"
insert overwrite table ads_user_retention
select * from ads_user_retention
union
select
    'do_date',
    create_date,
    datediff('do_date', create_date) retention_day,
    count(t2.user_id) retention_count,
    count(t1.user_id) new_user_count,
    cast(count(t2.user_id) / count(t1.user_id) *100 as decimal(16,2)) retention_rate
from
(
    select user_id,
           dt create_date
    from dwd_traffic_start_inc
    where dt >= date_sub('do_date', 7)
      and dt < 'do_date'
    and is_new = 1
) t1
left join
(
    select
        user_id
    from dwd_traffic_start_inc
    where dt = 'do_date'
    and is_new = 1
) t2
on t1.user_id = t2.user_id
group by create_date;
"
ads_user_stats"
insert overwrite table ads_user_stats
     select * from ads_user_stats
     union
select
       'do_date' dt,
       t1.recent_days,
       new_user_count,
       active_user_count
from (
         select recent_days,
                count(*) new_user_count
         from dwd_traffic_start_inc
            lateral view explode(`array`(1, 7, 30)) tmp as recent_days
         where dt > date_sub('do_date', 30)
           and dt > date_sub('do_date', recent_days)
             and is_new = 1
         group by recent_days
     ) t1
join
     (
         select recent_days,
                count(*) active_user_count
         from dws_user_user_login_td
            lateral view explode(`array`(1, 7, 30)) tmp as recent_days
         where dt = 'do_date'
           and login_date_last > date_sub('do_date', 30)
           and login_date_last > date_sub('do_date', recent_days)
         group by recent_days
     ) t2
on t1.recent_days = t2.recent_days;
"
ads_user_action"
insert overwrite table ads_user_action
select  * from ads_user_action
union
select
    'do_date' dt,
    t1.recent_days,
    home_count,
    good_detail_count,
    cart_count,
    order_count,
    payment_count
from
(
    select
           1        recent_days,
           count(*) payment_count
    from dws_trade_user_payment_1d
    where dt = 'do_date'
    union all
    select recent_days,
           `if`(recent_days = 7, sum(`if`(payment_count_7d > 0, 1, 0)), count(*)) payment_count
    from dws_trade_user_payment_nd
             lateral view explode(`array`(7, 30)) tmp as recent_days
    where dt = 'do_date'
    group by recent_days
) t1
join
(
    select
        1 recent_days,
        count(*) order_count
    from dws_trade_user_order_1d
    where dt = 'do_date'
    union all
    select
        recent_days,
        `if`(recent_days = 7,sum(`if`(order_count_7d > 0, 1 , 0)),count(*)) order_count
    from dws_trade_user_order_nd
    lateral view explode(`array`(7,30)) tmp as recent_days
    where dt = 'do_date'
    group by recent_days
) t2
on t1.recent_days = t2.recent_days
join
(
    select
        1 recent_days,
        count(*) cart_count
    from dws_trade_user_cart_add_1d
    where dt = 'do_date'
    union all
    select
        recent_days,
        `if`(recent_days = 7, sum(`if`(cart_add_count_7d > 0, 1 , 0)),count(*))
    from dws_trade_user_cart_add_nd
    lateral view explode(`array`(7,30)) tmp as recent_days
    where dt = 'do_date'
    group by recent_days
) t3
on t1.recent_days = t3.recent_days
join
(
    select
        1 recent_days,
        count(`if`(page_id = 'home', mid_id, null)) home_count,
        count(`if`(page_id = 'good_detail', mid_id, null)) good_detail_count
    from dws_traffic_page_visitor_page_view_1d
    where dt = 'do_date'
    union all
    select
        recent_days,
        sum(`if`(recent_days = 7,`if`(page_id = 'home' and view_count_7d > 0, 1 ,0),`if`(page_id = 'home', 1 ,0))) home_count,
        sum(`if`(recent_days = 7,`if`(page_id = 'good_detail' and view_count_7d > 0, 1 ,0),`if`(page_id = 'good_detail', 1 ,0))) good_detail_count
    from dws_traffic_page_visitor_page_view_nd
    lateral view explode(`array`(7,30)) tmp as recent_days
    where dt = 'do_date' and (page_id = 'home' or page_id = 'good_detail')
    group by recent_days
) t4
on t1.recent_days = t4.recent_days;
"
ads_class_course_order="
insert overwrite table ads_class_course_order
select 1 recent_days,
       course_id,
       course_name,
       dt,
       order_count_1d order_count,
       number_orders_1d number_orders,
       order_amount_1d order_amount
from dws_class_course_order_1d
where dt = '$do_date'
union all
select recent_days,
       course_id,
       course_name,
       dt,
       `if`(recent_days = 7, order_count_7d, order_count_30d) order_count,
       `if`(recent_days = 7, number_orders_7d, number_orders_30d) number_orders,
       `if`(recent_days = 7, order_amount_7d, order_amount_30d) order_amount
from dws_class_course_order_nd
         lateral view explode(`array`(7, 30)) tmp as recent_days
where dt = '$do_date';
"
ads_class_subject_order="
insert overwrite table ads_class_subject_order
select 1 recent_days,
       subject_id,
       subject_name,
       dt,
       sum(order_count_1d) order_count,
       sum(number_orders_1d) number_orders,
       sum(order_amount_1d) order_amount
from dws_class_course_order_1d
where dt = '$do_date'
group by subject_name,subject_id,dt,1
union all
select recent_days,
       course_id,
       course_name,
       dt,
       sum(`if`(recent_days = 7, order_count_7d, order_count_30d)) order_count,
       sum(`if`(recent_days = 7, number_orders_7d, number_orders_30d)) number_orders,
       sum(`if`(recent_days = 7, order_amount_7d, order_amount_30d)) order_amount
from dws_class_course_order_nd
         lateral view explode(`array`(7, 30)) tmp as recent_days
where dt = '$do_date'
group by recent_days, course_id, course_name, dt;
"
ads_class_category_order="
insert overwrite table ads_class_category_order
select 1 recent_days,
       category_id,
       category_name,
       dt,
       sum(order_count_1d) order_count,
       sum(number_orders_1d) number_orders,
       sum(order_amount_1d) order_amount
from dws_class_course_order_1d
where dt = '$do_date'
group by category_name,category_id,dt,1
union all
select recent_days,
       category_id,
       category_name,
       dt,
       sum(`if`(recent_days = 7, order_count_7d, order_count_30d)) order_count,
       sum(`if`(recent_days = 7, number_orders_7d, number_orders_30d)) number_orders,
       sum(`if`(recent_days = 7, order_amount_7d, order_amount_30d)) order_amount
from dws_class_course_order_nd
         lateral view explode(`array`(7, 30)) tmp as recent_days
where dt = '$do_date'
group by recent_days, category_id, category_name, dt;
"
ads_class_course_review="
insert overwrite table ads_class_course_review
select 1 recent_days,
       course_id,
       course_name,
       dt,
       review_avg_1d,
       review_user_count_1d,
       rate_1d
from dws_class_course_review_1d
where dt='$do_date'
union
select recent_days,
       course_id,
       course_name,
       dt,
       `if`(recent_days=7,review_avg_7d,review_avg_30d),
       `if`(recent_days=7,review_user_count_7d,review_user_count_30d),
       `if`(recent_days=7,rate_7d,rate_30d)
from dws_class_course_review_nd
lateral view explode(`array`(7,30)) tmp as recent_days
where dt='$do_date';
"
ads_class_course_audition="
select 1 recent_days,
       course_id,
       course_name,
       dt,
       audition_count_1d,
       audition_retention_rates_1d
from dws_class_course_audition_1d
where dt='$do_date'
union
select recent_days,
       course_id,
       course_name,
       dt,
       `if`(recent_days=7,audition_count_7d,audition_count_30d),
       `if`(recent_days=7,audition_retention_rates_7d,audition_retention_rates_30d)
from dws_class_course_audition_nd
lateral view explode(`array`(7,30)) tmp as recent_days
where dt='$do_date';
"
ads_class_category_audition="
insert overwrite table ads_class_category_audition
select 1 recent_days,
       category_id,
       category_name,
       dt,
       sum(audition_count_1d),
       sum(audition_retention_rates_1d)
from dws_class_course_audition_1d
where dt='$do_date'
group by category_id, 1, category_name, dt
union
select recent_days,
       category_id,
       category_name,
       dt,
       sum(`if`(recent_days=7,audition_count_7d,audition_count_30d)),
       sum(`if`(recent_days=7,audition_retention_rates_7d,audition_retention_rates_30d))
from dws_class_course_audition_nd
lateral view explode(`array`(7,30)) tmp as recent_days
where dt='$do_date'
group by category_id, recent_days, category_name, dt;
"
ads_class_subject_audition="
insert overwrite table ads_class_subject_audition
select 1 recent_days,
       subject_id,
       subject_name,
       dt,
       sum(audition_count_1d),
       sum(audition_retention_rates_1d)
from dws_class_course_audition_1d
where dt='$do_date'
group by subject_name, 1, subject_id, dt
union
select recent_days,
       subject_id,
       subject_name,
       dt,
       sum(`if`(recent_days=7,audition_count_7d,audition_count_30d)),
       sum(`if`(recent_days=7,audition_retention_rates_7d,audition_retention_rates_30d))
from dws_class_course_audition_nd
lateral view explode(`array`(7,30)) tmp as recent_days
where dt='$do_date'
group by subject_id, recent_days, subject_name, dt;
"


ads_trade_province_order = "insert overwrite table ads_trade_province_order
select
* from
    ads_trade_province_order
    union
select
       '2022-07-05' dt,
       recent_days,
       province_name,
       sum(user_count_1d) user_count,
       sum(order_count_1d)  order_count,
       sum(order_total_amount_1d)  order_total_amount
from dws_trade_province_order_1d
lateral view explode(`array`(1, 7, 30)) tmp as recent_days
where dt > date_sub('2022-07-05',30)
and dt > date_sub('2022-07-05',recent_days)
and isnotnull(province_name)
group by dt,recent_days,province_name;
"

ads_trade_user_order = "
insert overwrite table ads_trade_user_order
select * from ads_trade_user_order
union
select
      '2022-07-05' dt,
       recent_days,
       sum(user_count_1d) user_count,
       sum(order_count_1d)  order_count,
       sum(order_total_amount_1d)  order_total_amount
from dws_trade_user_order_1d
lateral view explode(`array`(1, 7, 30)) tmp as recent_days
where dt > date_sub('2022-07-05',30)
and dt > date_sub('2022-07-05',recent_days)
group by dt,recent_days;"


ads_examine_course_exam ="
insert overwrite table ads_examine_course_exam
select *
from ads_examine_course_exam
union
select
       '2022-07-05' dt,
       recent_days,
       sum(user_count_1d)  user_count,
       sum(score_avg_1d)/recent_days  			score_avg,
       sum(duration_sec_avg_1d)/recent_days 	duration_sec_avg
from dws_examine_course_exam_1d
lateral view explode(`array`(1, 7, 30)) tmp as recent_days
where dt > date_sub('2022-07-05',30)
and dt > date_sub('2022-07-05',recent_days)
group by dt,recent_days;"







case $2 in
"ads_play_video_by_chapter")
	hive --database gmall -e "$ads_play_video_by_chapter"
	;;
"ads_play_video_by_course")
	hive --database gmall -e "$ads_play_video_by_course"
	;;
"ads_play_course_by_course")
	hive --database gmall -e "$ads_play_course_by_course"
	;;
"ads_play_course")
	hive --database gmall -e "$ads_play_course"
	;;
"ads_traffic_stats_by_channel")
	hive --database gmall -e "$ads_traffic_stats_by_channel"
	;;
"ads_page_path")
	hive --database gmall -e "$ads_page_pathl"
	;;
"ads_user_change")
	hive --database gmall -e "$ads_user_change"
	;;
"ads_user_retention")
	hive --database gmall -e "$ads_user_retention"
	;;
"ads_user_stats")
	hive --database gmall -e "$ads_user_stats"
	;;
"ads_user_action")
	hive --database gmall -e "$ads_user_action"
	;;
"ads_class_course_order")
	hive --database gmall -e "$ads_class_course_order"
	;;
"ads_class_subject_order")
	hive --database gmall -e "$ads_class_subject_order"
	;;
"ads_class_category_order")
	hive --database gmall -e "$ads_class_category_order"
	;;
"ads_class_course_review")
	hive --database gmall -e "$ads_class_course_review"
	;;
"ads_class_course_audition")
	hive --database gmall -e "$ads_class_course_audition"
	;;
"ads_class_category_audition")
	hive --database gmall -e "$ads_class_category_audition"
	;;
"ads_class_subject_audition")
	hive --database gmall -e "$ads_class_subject_audition"
	;;
"ads_trade_province_order")
	hive --database gmall -e "$ads_trade_province_order"
	;;
"ads_trade_user_order")
	hive --database gmall -e "$ads_trade_user_order"
	;;
"ads_examine_course_exam")
	hive --database gmall -e "$ads_examine_course_exam"
	;;
    
    
"all")
	hive --database gmall -e "$ads_play_video_by_chapter$ads_play_video_by_course$ads_play_course_by_course$ads_play_course$ads_user_change$ads_user_retention$ads_user_stats$ads_user_action$ads_class_course_order$ads_class_subject_order
    $ads_class_category_order$ads_class_course_review$ads_class_course_audition$ads_class_category_audition$ads_class_subject_audition$ads_trade_province_order$ads_trade_user_order$ads_examine_course_exam"
	;;
esac